﻿using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Web.UI.WebControls;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 仓库管理
    /// </summary>
    public class BCkGl
    {
        /// <summary>
        /// 根据[查询条件]查询物资入库登记总记录数
        /// </summary>
        public static DataSet GetWzRkDjByCxCount(string sRkLx, string sWhere, string sDjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery("SELECT COUNT(1) FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0)", sDjr, sRkLx);
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND {0}", BGlobal.BuildLike(sWhere, "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号")), sDjr, sRkLx);
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库登记
        /// </summary>
        public static DataSet GetWzRkDjByCx2(string sRkLx, string sWhere, string sDjr, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,0 单价,A.数量,A.备注,A.编辑人 FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 物资入库V B WHERE B.状态=0 AND B.入库类型=@P1 AND (@P0='' OR B.编辑人=@P0) ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)), sDjr, sRkLx);
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,0 单价,A.数量,A.备注,A.编辑人 FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资入库V B WHERE B.状态=0 AND B.入库类型=@P1 AND (@P0='' OR B.编辑人=@P0) AND {3} ORDER BY B.供货单位编号,B.物资编号) ORDER BY A.供货单位编号,A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.供货单位", "B.物资编号", "B.物资名称", "B.规格型号")), sDjr, sRkLx);
                }
            }
        }
        public static DataSet GetWzRkDjByCx(string sRkLx, string sWhere, string sDjr, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,A.单价,A.数量,A.备注,A.编辑人 FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 物资入库V B WHERE B.状态=0 AND B.入库类型=@P1 AND (@P0='' OR B.编辑人=@P0) ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)), sDjr, sRkLx);
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,A.单价,A.数量,A.备注,A.编辑人 FROM 物资入库V A WHERE A.状态=0 AND A.入库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资入库V B WHERE B.状态=0 AND B.入库类型=@P1 AND (@P0='' OR B.编辑人=@P0) AND {3} ORDER BY B.供货单位编号,B.物资编号) ORDER BY A.供货单位编号,A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.供货单位", "B.物资编号", "B.物资名称", "B.规格型号")), sDjr, sRkLx);
                }
            }
        }

        /// <summary>
        /// 根据[物资入库ID]获得物资入库登记
        /// </summary>
        public static DataSet GetWzRkDjById(int iWzRkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,供货单位编号,供货单位,物资编号,物资名称,规格型号,单位,单价,数量,金额,存放区域,备注,编辑人,编辑时间 FROM 物资入库V WHERE ID=@P0 AND 状态=0", iWzRkId);
            }
        }

        /// <summary>
        /// 保存物资入库登记
        /// </summary>
        public static string SaveWzRkDj(int iWzRkId, string sRkLx, string sGhdwBh, string sWzBh, decimal nDj, decimal nSl, string sCfqy, string sBz, string sBjr)
        {
            if (sGhdwBh == string.Empty || sWzBh == string.Empty)
            {
                return "供货单位、物资编号、名称不能为空！";
            }
            if (nSl == 0m)
            {
                return "入库数量不能为零！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sRkLx == "产品登记") // 自动计算产品单价
                {
                    DataSet dsYz = da.ExecuteQuery("SELECT COUNT(1) 总记录,SUM(CASE WHEN ISNULL(参考单价,0)=0 THEN 0 ELSE 1 END) 有效记录,SUM(ISNULL(参考单价,0)*装入数量) 有效金额 FROM(SELECT C.参考单价,A.装入数量 FROM 产品设计 B LEFT JOIN 物资库存 C ON B.代号=C.物资编号,产品明细 A,产品设计 D WHERE A.零件ID=B.ID AND A.产品ID=D.ID AND D.代号=@P0 UNION ALL SELECT C.参考单价,A.装入数量 FROM 购件目录 B LEFT JOIN 物资库存 C ON B.代号=C.物资编号,产品外购 A,产品设计 D WHERE A.购件ID=B.ID AND A.产品ID=D.ID AND D.代号=@P0) E", sWzBh);
                    if (dsYz == null || (int)dsYz.Tables[0].Rows[0]["总记录"] == 0 || (int)dsYz.Tables[0].Rows[0]["总记录"] != (int)dsYz.Tables[0].Rows[0]["有效记录"])
                    {
                        dsYz = da.ExecuteQuery("SELECT E.代号 FROM(SELECT B.代号 FROM 产品设计 B LEFT JOIN 物资库存 C ON B.代号=C.物资编号,产品明细 A,产品设计 D WHERE A.零件ID=B.ID AND A.产品ID=D.ID AND D.代号=@P0 AND ISNULL(C.参考单价,0)=0 UNION ALL SELECT B.代号 FROM 购件目录 B LEFT JOIN 物资库存 C ON B.代号=C.物资编号,产品外购 A,产品设计 D WHERE A.购件ID=B.ID AND A.产品ID=D.ID AND D.代号=@P0 AND ISNULL(C.参考单价,0)=0) E ORDER BY E.代号", sWzBh);
                        string strDh = string.Empty;
                        for (int i = 0; i < dsYz.Tables[0].Rows.Count; i++)
                        {
                            strDh += dsYz.Tables[0].Rows[i]["代号"].ToString() + ",";
                        }
                        return "不能登记，该产品的零部件或外购件[" + strDh + "]未全部正式入库！";
                    }
                    nDj = Math.Round(Convert.ToDecimal(dsYz.Tables[0].Rows[0]["有效金额"]), 8);
                }
                if (nDj == 0m)
                {
                    return "入库单价不能为零！";
                }
                if (iWzRkId == 0)
                {
                    if (sRkLx == "产品登记")
                    {
                        if (sWzBh.Substring(0, 4).ToUpper() != "HIAU" || Convert.ToInt32(sWzBh.Substring(4, 1)) > 6)
                        {
                            return "添加物资失败，该物资不属于产品！";
                        }
                    }
                    else if (sRkLx == "采购登记")
                    {
                        if (sWzBh.Substring(0, 4).ToUpper() == "HIAU" && Convert.ToInt32(sWzBh.Substring(4, 1)) <= 6)
                        {
                            return "添加物资失败，该物资属于产品！";
                        }
                    }
                    if (da.Execute("INSERT INTO 物资入库(入库类型,供货单位ID,物资ID,物资编号,单价,数量,金额,存放区域,备注,编辑人,编辑时间,状态) SELECT @P9,(SELECT ID FROM 供货单位 WHERE 编号=@P0),ID,编号,@P2,@P3,@P4,@P5,@P6,@P7,@P8,0 FROM 物资目录V WHERE 编号=@P1 AND EXISTS(SELECT 1 FROM 供货单位 WHERE 编号=@P0) AND NOT EXISTS(SELECT 1 FROM 物资入库V WHERE 状态=0 AND 入库类型=@P9 AND 供货单位编号=@P0 AND 物资编号=@P1)", sGhdwBh, sWzBh, nDj, nSl, Math.Round(nDj * nSl, 2), sCfqy.Trim(), sBz.Trim(), sBjr, DateTime.Now, sRkLx) <= 0)
                    {
                        return "添加物资失败，可能已存在！";
                    }
                }
                else
                {
                    if (da.Execute("UPDATE 物资入库 SET 单价=@P1,数量=@P2,金额=@P3,存放区域=@P4,备注=@P5,编辑时间=@P6 WHERE ID=@P0 AND 状态=0", iWzRkId, nDj, nSl, Math.Round(nDj * nSl, 2), sCfqy.Trim(), sBz.Trim(), DateTime.Now) <= 0)
                    {
                        return "保存物资失败！";
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[物资入库ID]删除物资入库登记
        /// </summary>
        public static string DelWzRkDj(int iWzRkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 物资入库 WHERE ID=@P0 AND 状态=0", iWzRkId) <= 0)
                {
                    return "删除物资失败，可能已提交！";
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库
        /// </summary>
        public static DataSet GetWzRkByCx(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.入库类型,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,A.数量,A.备注,A.编辑人 FROM 物资入库V A WHERE A.状态=0 AND {0} ORDER BY A.供货单位编号,A.物资编号", BGlobal.BuildLike(sWhere, "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号")));
            }
        }

        /// <summary>
        /// 根据[物资入库ID]获得物资入库
        /// </summary>
        public static DataSet GetWzRkById(int iWzRkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,入库类型,供货单位编号,供货单位,物资编号,物资名称,规格型号,单位,单价,数量,金额,存放区域,备注,编辑人,编辑时间 FROM 物资入库V WHERE ID=@P0 AND 状态=0", iWzRkId);
            }
        }

        /// <summary>
        /// 保存物资入库
        /// </summary>
        public static string SaveWzRk(int iWzRkId, string sCfqy, string sBz)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 物资入库 SET 存放区域=@P1,备注=@P2,编辑时间=@P3 WHERE ID=@P0 AND 状态=0", iWzRkId, sCfqy.Trim(), sBz.Trim(), DateTime.Now) <= 0)
                {
                    return "保存物资失败！";
                }
                return null;
            }
        }

        /// <summary>
        /// 执行物资入库
        /// </summary>
        public static string ExeWzRk(string sRkWzs, string sCzr, ref string sWzRkBh)
        {
            if (sRkWzs == string.Empty)
            {
                return "没有需要入库的物资！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                using (DSqlTrans daTrans = da.BeginTrans())
                {
                    DateTime dCzsj = DateTime.Now;
                    string sRkBh = string.Format("RK{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    string sCkBh = string.Format("CK{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    string sRcBh = string.Format("RC{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    DataSet ds1 = daTrans.ExecuteQuery("SELECT CAST(CASE WHEN ISNULL(MAX(LEFT(入库编号,10)),@P0)=@P0 THEN 1 ELSE 0 END AS BIT) FROM 物资入库 WHERE 状态=9 AND 结转时间 IS NULL", sRkBh);
                    if (ds1 == null || !((bool)ds1.Tables[0].Rows[0][0]))
                    {
                        daTrans.Rollback();
                        if (ExeCwJz(dCzsj) == null)
                        {
                            // 重新执行入库
                            return ExeWzRk(sRkWzs, sCzr, ref sWzRkBh);
                        }
                        return "物资入库失败，跨月未结转！";
                    }
                    DataSet ds2 = daTrans.ExecuteQuery("SELECT CAST(CASE WHEN ISNULL(MAX(LEFT(出库编号,10)),@P0)=@P0 THEN 1 ELSE 0 END AS BIT) FROM 物资出库 WHERE 状态=9 AND 结转时间 IS NULL", sCkBh);
                    if (ds2 == null || !((bool)ds2.Tables[0].Rows[0][0]))
                    {
                        daTrans.Rollback();
                        if (ExeCwJz(dCzsj) == null)
                        {
                            // 重新执行入库
                            return ExeWzRk(sRkWzs, sCzr, ref sWzRkBh);
                        }
                        return "物资入库失败，跨月未结转！";
                    }
                    DataSet ds = daTrans.ExecuteQuery(string.Format("SELECT 物资编号 FROM 物资入库 WHERE 状态=0 AND ID IN ({0}) GROUP BY 入库类型,供货单位ID,物资编号 HAVING COUNT(1)>1", sRkWzs));
                    if (ds == null || ds.Tables[0].Rows.Count > 0)
                    {
                        string sWz = string.Empty;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            sWz += (string)ds.Tables[0].Rows[i]["物资编号"] + "、";
                        }
                        return string.Format("物资入库失败，部分物资{0}存在重复！", sWz.Substring(0, sWz.Length - 1));
                    }
                    ds = daTrans.ExecuteQuery(string.Format("SELECT DISTINCT 入库类型,供货单位ID FROM 物资入库 WHERE 状态=0 AND ID IN ({0})", sRkWzs));
                    if (ds == null || ds.Tables[0].Rows.Count != 1)
                    {
                        return "物资入库失败，可能选择了多个供应商！";
                    }
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string sRkLx = (string)ds.Tables[0].Rows[i]["入库类型"];
                        int iGhDwId = (int)ds.Tables[0].Rows[i]["供货单位ID"];
                        // 写入库存
                        if (daTrans.Execute(string.Format("UPDATE 物资库存 SET 入库数量=物资库存.入库数量+A.数量,入库金额=物资库存.入库金额+A.金额,库存数量=物资库存.库存数量+A.数量,库存金额=物资库存.库存金额+A.金额,参考单价=A.单价,存放区域=(CASE WHEN A.存放区域='' THEN 物资库存.存放区域 ELSE A.存放区域 END),最后供货=A.供货单位ID,最后入库=@P2 FROM 物资库存,物资入库 A WHERE 物资库存.物资编号=A.物资编号 AND A.状态=0 AND A.ID IN ({0}) AND A.入库类型=@P0 AND A.供货单位ID=@P1", sRkWzs), sRkLx, iGhDwId, dCzsj) < 0)
                        {
                            return "物资入库失败！";
                        }
                        if (daTrans.Execute(string.Format("INSERT INTO 物资库存(物资ID,物资编号,期初数量,期初金额,入库数量,入库金额,出库数量,出库金额,库存数量,库存金额,参考单价,存放区域,最后供货,最后入库) SELECT A.物资ID,A.物资编号,0,0,A.数量,A.金额,0,0,A.数量,A.金额,A.单价,A.存放区域,A.供货单位ID,@P2 FROM 物资入库 A WHERE A.状态=0 AND A.ID IN ({0}) AND A.入库类型=@P0 AND A.供货单位ID=@P1 AND NOT EXISTS(SELECT 1 FROM 物资库存 B WHERE B.物资编号=A.物资编号)", sRkWzs), sRkLx, iGhDwId, dCzsj) < 0)
                        {
                            return "物资入库失败！";
                        }
                        // 写入台账
                        if (daTrans.Execute(string.Format("INSERT INTO 物资台账(物资ID,物资编号,入出编号,摘要说明,物资入库ID,结存数量,结存金额,入出时间) SELECT B.物资ID,B.物资编号,@P2+(SELECT REPLACE(STR(ISNULL(MAX(RIGHT(入出编号,4)),0)+1,4),' ','0') FROM 物资台账 WHERE 入出编号 LIKE @P2+'%'),A.入库类型,A.ID,B.库存数量,B.库存金额,@P3 FROM 物资入库 A,物资库存 B WHERE A.物资编号=B.物资编号 AND A.状态=0 AND A.ID IN ({0}) AND A.入库类型=@P0 AND A.供货单位ID=@P1", sRkWzs), sRkLx, iGhDwId, sRcBh, dCzsj) <= 0)
                        {
                            return "物资入库失败！";
                        }
                        // 更新入库
                        if (daTrans.Execute(string.Format("UPDATE 物资入库 SET 入库编号=@P2+(SELECT REPLACE(STR(ISNULL(MAX(RIGHT(入库编号,4)),0)+1,4),' ','0') FROM 物资入库 WHERE 状态=9 AND 入库编号 LIKE @P2+'%'),状态=9,审核人=@P3,审核时间=@P4 WHERE 状态=0 AND ID IN ({0}) AND 入库类型=@P0 AND 供货单位ID=@P1", sRkWzs), sRkLx, iGhDwId, sRkBh, sCzr, dCzsj) <= 0)
                        {
                            return "物资入库失败！";
                        }
                        // 返回入库编号
                        sWzRkBh = daTrans.ExecuteQuery("SELECT MAX(入库编号) FROM 物资入库 WHERE 状态=9 AND 入库编号 LIKE @P0+'%'", sRkBh).Tables[0].Rows[0][0].ToString();
                    }
                    if (!daTrans.Commit())
                    {
                        return "物资入库失败！";
                    }
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库查询总记录数
        /// </summary>
        public static DataSet GetWzRkCxByCxCount(DateTime dKsrq, DateTime dJsrq, string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资入库V A WHERE A.状态=9 AND A.审核时间 BETWEEN @P0 AND @P1 AND {0}", BGlobal.BuildLike(sWhere, "A.入库编号", "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号")), dKsrq, dJsrq);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库查询
        /// </summary>
        public static DataSet GetWzRkCxByCx(DateTime dKsrq, DateTime dJsrq, string sWhere, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.入库编号,A.审核时间,A.供货单位,A.物资编号,A.物资名称,A.规格型号,A.单位,A.单价,A.数量,A.金额 FROM 物资入库V A WHERE A.状态=9 AND A.审核时间 BETWEEN @P0 AND @P1 AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资入库V B WHERE B.状态=9 AND B.审核时间 BETWEEN @P0 AND @P1 AND {3} ORDER BY B.入库编号,B.物资编号) ORDER BY A.入库编号,A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.入库编号", "A.供货单位", "A.物资编号", "A.物资名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.入库编号", "B.供货单位", "B.物资编号", "B.物资名称", "B.规格型号")), dKsrq, dJsrq);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资出库登记总记录数
        /// </summary>
        public static DataSet GetWzCkDjByCxCount(string sCkLx, string sWhere, string sDjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery("SELECT COUNT(1) FROM 物资出库V A WHERE A.状态=0 AND A.出库类型=@P1 AND (@P0='' OR A.编辑人=@P0)", sDjr, sCkLx);
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资出库V A WHERE A.状态=0 AND A.出库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND {0}", BGlobal.BuildLike(sWhere, "A.经领人", "A.物资编号", "A.物资名称", "A.规格型号", "A.用途")), sDjr, sCkLx);
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资出库登记
        /// </summary>
        public static DataSet GetWzCkDjByCx(string sCkLx, string sWhere, string sDjr, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.经领人,A.物资编号,A.物资名称,A.规格型号,A.单位,A.原始数量,A.用途,A.备注,A.编辑人 FROM 物资出库V A WHERE A.状态=0 AND A.出库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 物资出库V B WHERE B.状态=0 AND B.出库类型=@P1 ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)), sDjr, sCkLx);
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.经领人,A.物资编号,A.物资名称,A.规格型号,A.单位,A.原始数量,A.用途,A.备注,A.编辑人 FROM 物资出库V A WHERE A.状态=0 AND A.出库类型=@P1 AND (@P0='' OR A.编辑人=@P0) AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资出库V B WHERE B.状态=0 AND B.出库类型=@P1 AND (@P0='' OR B.编辑人=@P0) AND {3} ORDER BY B.经领人,B.物资编号) ORDER BY A.经领人,A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.经领人", "A.物资编号", "A.物资名称", "A.规格型号", "A.用途"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.经领人", "B.物资编号", "B.物资名称", "B.规格型号", "B.用途")), sDjr, sCkLx);
                }
            }
        }

        /// <summary>
        /// 根据[物资出库ID]获得物资出库登记
        /// </summary>
        public static DataSet GetWzCkDjById(int iWzCkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,经领人,物资编号,物资名称,规格型号,单位,单价,原始数量,金额,用途,备注,编辑人,编辑时间 FROM 物资出库V WHERE ID=@P0 AND 状态=0", iWzCkId);
            }
        }

        /// <summary>
        /// 保存物资出库登记
        /// </summary>
        public static string SaveWzCkDj(int iWzCkId, string sCkLx, string sJlr, string sWzBh, decimal nSl, string sYt, string sBz, string sBjr)
        {
            if (sJlr.Trim() == string.Empty || sWzBh == string.Empty)
            {
                return "经领人、物资编号、名称不能为空！";
            }
            if (nSl == 0m)
            {
                return "领用数量不能为零！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (iWzCkId == 0)
                {
                    if (da.Execute("INSERT INTO 物资出库(出库类型,经领人,物资ID,物资编号,单价,原始数量,数量,金额,用途,备注,编辑人,编辑时间,状态) SELECT @P7,@P0,ID,编号,0,@P2,@P2,0,@P3,@P4,@P5,@P6,0 FROM 物资目录V WHERE 编号=@P1 AND NOT EXISTS(SELECT 1 FROM 物资出库V WHERE 状态=0 AND 出库类型=@P7 AND 经领人=@P0 AND 用途=@P3 AND 物资编号=@P1) AND EXISTS(SELECT 1 FROM 用户列表 WHERE 姓名=@P0) AND EXISTS(SELECT 1 FROM 物资用途 WHERE 物资用途=@P3)", sJlr.Trim(), sWzBh, nSl, sYt.Trim(), sBz.Trim(), sBjr, DateTime.Now, sCkLx) <= 0)
                    {
                        return "添加物资失败，可能已存在！";
                    }
                }
                else
                {
                    if (da.Execute("UPDATE 物资出库 SET 原始数量=@P1,数量=@P1,用途=@P2,备注=@P3,编辑时间=@P4 WHERE ID=@P0 AND 状态=0", iWzCkId, nSl, sYt.Trim(), sBz.Trim(), DateTime.Now) <= 0)
                    {
                        return "保存物资失败！";
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[物资出库ID]删除物资出库登记
        /// </summary>
        public static string DelWzCkDj(int iWzCkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 物资出库 WHERE ID=@P0 AND 状态=0", iWzCkId) <= 0)
                {
                    return "删除物资失败，可能已提交！";
                }
                return null;
            }
        }

        /// <summary>
        /// 导入物资出库登记
        /// </summary>
        public static string ImportWzCkDj(string sFileName, string sCkLx, string sBjr)
        {
            DataSet ds = null;
            using (DOleDb da = new DOleDb("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + BGlobal.AttachmentPath + sFileName + "\";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""))
            {
                ds = da.ExecuteQuery("SELECT 代号,领用数量,经领人,用途,备注 FROM [Sheet1$]");
            }
            if (ds == null)
            {
                return "导入物资出库登记失败，可能格式不正确！";
            }
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                SaveWzCkDj(0, sCkLx, Convert.ToString(ds.Tables[0].Rows[i]["经领人"]), Convert.ToString(ds.Tables[0].Rows[i]["代号"]), Convert.ToDecimal(ds.Tables[0].Rows[i]["领用数量"]), Convert.ToString(ds.Tables[0].Rows[i]["用途"]), Convert.ToString(ds.Tables[0].Rows[i]["备注"]), sBjr);
            }
            return "导入物资出库登记成功！";
        }

        /// <summary>
        /// 根据[查询条件]查询物资出库
        /// </summary>
        public static DataSet GetWzCkByCx(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.经领人,A.物资编号,A.物资名称,A.规格型号,A.单位,B.库存数量,A.原始数量,A.数量,A.用途,A.编辑人 FROM 物资出库V A LEFT JOIN 物资库存 B ON B.物资编号=A.物资编号 WHERE A.状态=0 AND {0} ORDER BY A.经领人,A.物资编号", BGlobal.BuildLike(sWhere, "A.经领人", "A.物资编号", "A.物资名称", "A.规格型号", "A.用途")));
            }
        }

        /// <summary>
        /// 根据[物资出库ID]获得物资出库
        /// </summary>
        public static DataSet GetWzCkById(int iWzCkId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.出库类型,A.经领人,A.物资编号,A.物资名称,A.规格型号,A.单位,B.库存数量,A.原始数量,A.数量,A.用途,A.备注,A.编辑人,A.编辑时间 FROM 物资出库V A LEFT JOIN 物资库存 B ON B.物资编号=A.物资编号 WHERE A.ID=@P0 AND A.状态=0", iWzCkId);
            }
        }

        /// <summary>
        /// 保存物资出库
        /// </summary>
        public static string SaveWzCk(int iWzCkId, decimal nYsSl, decimal nSl, string sBz)
        {
            if (nSl != nYsSl)
            {
                if (nSl <= 0m || nSl > nYsSl)
                {
                    return "实发数量必须大于零且小于等于申领数量！";
                }
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 物资出库 SET 数量=@P1,备注=@P2,编辑时间=@P3 WHERE ID=@P0 AND 状态=0", iWzCkId, nSl, sBz.Trim(), DateTime.Now) <= 0)
                {
                    return "保存物资失败！";
                }
                return null;
            }
        }

        /// <summary>
        /// 执行物资出库
        /// </summary>
        public static string ExeWzCk(string sCkWzs, string sCzr, ref string sWzCkBh)
        {
            if (sCkWzs == string.Empty)
            {
                return "没有需要出库的物资！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                using (DSqlTrans daTrans = da.BeginTrans())
                {
                    DateTime dCzsj = DateTime.Now;
                    string sRkBh = string.Format("RK{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    string sCkBh = string.Format("CK{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    string sRcBh = string.Format("RC{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    DataSet ds1 = daTrans.ExecuteQuery("SELECT CAST(CASE WHEN ISNULL(MAX(LEFT(入库编号,10)),@P0)=@P0 THEN 1 ELSE 0 END AS BIT) FROM 物资入库 WHERE 状态=9 AND 结转时间 IS NULL", sRkBh);
                    if (ds1 == null || !((bool)ds1.Tables[0].Rows[0][0]))
                    {
                        daTrans.Rollback();
                        if (ExeCwJz(dCzsj) == null)
                        {
                            // 重新执行出库
                            return ExeWzCk(sCkWzs, sCzr, ref sWzCkBh);
                        }
                        return "物资出库失败，跨月未结转！";
                    }
                    DataSet ds2 = daTrans.ExecuteQuery("SELECT CAST(CASE WHEN ISNULL(MAX(LEFT(出库编号,10)),@P0)=@P0 THEN 1 ELSE 0 END AS BIT) FROM 物资出库 WHERE 状态=9 AND 结转时间 IS NULL", sCkBh);
                    if (ds2 == null || !((bool)ds2.Tables[0].Rows[0][0]))
                    {
                        daTrans.Rollback();
                        if (ExeCwJz(dCzsj) == null)
                        {
                            // 重新执行出库
                            return ExeWzCk(sCkWzs, sCzr, ref sWzCkBh);
                        }
                        return "物资出库失败，跨月未结转！";
                    }
                    DataSet ds = daTrans.ExecuteQuery(string.Format("SELECT 物资编号 FROM 物资出库 WHERE 状态=0 AND ID IN ({0}) GROUP BY 出库类型,经领人,用途,物资编号 HAVING COUNT(1)>1", sCkWzs));
                    if (ds == null || ds.Tables[0].Rows.Count > 0)
                    {
                        string sWz = string.Empty;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            sWz += (string)ds.Tables[0].Rows[i]["物资编号"] + "、";
                        }
                        return string.Format("物资出库失败，部分物资{0}存在重复！", sWz.Substring(0, sWz.Length - 1));
                    }
                    ds = daTrans.ExecuteQuery(string.Format("SELECT A.物资编号 FROM 物资出库 A LEFT JOIN 物资库存 B ON A.物资编号=B.物资编号 WHERE A.状态=0 AND A.ID IN ({0}) GROUP BY A.物资编号 HAVING SUM(A.数量)>ISNULL(MIN(B.库存数量),0)", sCkWzs));
                    if (ds == null || ds.Tables[0].Rows.Count > 0)
                    {
                        string sWz = string.Empty;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            sWz += (string)ds.Tables[0].Rows[i]["物资编号"] + "、";
                        }
                        return string.Format("物资出库失败，部分物资{0}库存数量不足！", sWz.Substring(0, sWz.Length - 1));
                    }
                    ds = daTrans.ExecuteQuery(string.Format("SELECT DISTINCT 出库类型,经领人,用途 FROM 物资出库 WHERE 状态=0 AND ID IN ({0})", sCkWzs));
                    if (ds == null || ds.Tables[0].Rows.Count != 1)
                    {
                        return "物资出库失败，可能选择了多个经领人及用途！";
                    }
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string sCkLx = (string)ds.Tables[0].Rows[i]["出库类型"];
                        string sJlr = (string)ds.Tables[0].Rows[i]["经领人"];
                        string sYt = (string)ds.Tables[0].Rows[i]["用途"];
                        // 写入金额
                        if (daTrans.Execute(string.Format("UPDATE 物资出库 SET 金额=(CASE WHEN A.库存数量=0 THEN ROUND(A.参考单价*物资出库.数量,2) WHEN 物资出库.数量=A.库存数量 THEN A.库存金额 ELSE ROUND(A.库存金额*物资出库.数量/A.库存数量,2) END),单价=(CASE WHEN A.库存数量=0 THEN A.参考单价 ELSE ROUND((CASE WHEN 物资出库.数量=A.库存数量 THEN A.库存金额 ELSE ROUND(A.库存金额*物资出库.数量/A.库存数量,2) END)/物资出库.数量,8) END) FROM 物资出库,物资库存 A WHERE 物资出库.物资编号=A.物资编号 AND 物资出库.状态=0 AND 物资出库.ID IN ({0}) AND 物资出库.出库类型=@P0 AND 物资出库.经领人=@P1 AND 物资出库.用途=@P2", sCkWzs), sCkLx, sJlr, sYt) <= 0)
                        {
                            return "物资出库失败！";
                        }
                        // 写入库存
                        if (daTrans.Execute(string.Format("UPDATE 物资库存 SET 出库数量=物资库存.出库数量+A.数量,出库金额=物资库存.出库金额+A.金额,库存数量=物资库存.库存数量-A.数量,库存金额=物资库存.库存金额-A.金额,最后出库=@P3 FROM 物资库存,物资出库 A WHERE 物资库存.物资编号=A.物资编号 AND A.状态=0 AND A.ID IN ({0}) AND A.出库类型=@P0 AND A.经领人=@P1 AND A.用途=@P2", sCkWzs), sCkLx, sJlr, sYt, dCzsj) <= 0)
                        {
                            return "物资出库失败！";
                        }
                        // 写入台账
                        if (daTrans.Execute(string.Format("INSERT INTO 物资台账(物资ID,物资编号,入出编号,摘要说明,物资出库ID,结存数量,结存金额,入出时间) SELECT B.物资ID,B.物资编号,@P3+(SELECT REPLACE(STR(ISNULL(MAX(RIGHT(入出编号,4)),0)+1,4),' ','0') FROM 物资台账 WHERE 入出编号 LIKE @P3+'%'),A.出库类型,A.ID,B.库存数量,B.库存金额,@P4 FROM 物资出库 A,物资库存 B WHERE A.物资编号=B.物资编号 AND A.状态=0 AND A.ID IN ({0}) AND A.出库类型=@P0 AND A.经领人=@P1 AND A.用途=@P2", sCkWzs), sCkLx, sJlr, sYt, sRcBh, dCzsj) <= 0)
                        {
                            return "物资出库失败！";
                        }
                        // 生成剩余
                        if (daTrans.Execute(string.Format("INSERT INTO 物资出库(出库类型,经领人,物资ID,物资编号,单价,原始数量,数量,金额,用途,备注,编辑人,编辑时间,状态) SELECT 出库类型,经领人,物资ID,物资编号,0,原始数量-数量,原始数量-数量,0,用途,备注+'[剩余生成]',编辑人,编辑时间,0 FROM 物资出库 WHERE 原始数量-数量>0 AND 状态=0 AND ID IN ({0}) AND 出库类型=@P0 AND 经领人=@P1 AND 用途=@P2 AND EXISTS(SELECT 1 FROM 物资库存 A WHERE A.物资编号=物资出库.物资编号)", sCkWzs), sCkLx, sJlr, sYt) < 0)
                        {
                            return "物资出库失败！";
                        }
                        // 更新出库
                        if (daTrans.Execute(string.Format("UPDATE 物资出库 SET 出库编号=@P3+(SELECT REPLACE(STR(ISNULL(MAX(RIGHT(出库编号,4)),0)+1,4),' ','0') FROM 物资出库 WHERE 状态=9 AND 出库编号 LIKE @P3+'%'),状态=9,审核人=@P4,审核时间=@P5 WHERE 状态=0 AND ID IN ({0}) AND 出库类型=@P0 AND 经领人=@P1 AND 用途=@P2 AND EXISTS(SELECT 1 FROM 物资库存 A WHERE A.物资编号=物资出库.物资编号)", sCkWzs), sCkLx, sJlr, sYt, sCkBh, sCzr, dCzsj) <= 0)
                        {
                            return "物资出库失败！";
                        }
                        // 返回出库编号
                        sWzCkBh = daTrans.ExecuteQuery("SELECT MAX(出库编号) FROM 物资出库 WHERE 状态=9 AND 出库编号 LIKE @P0+'%'", sCkBh).Tables[0].Rows[0][0].ToString();
                    }
                    if (!daTrans.Commit())
                    {
                        return "物资出库失败！";
                    }
                    return null;
                }
            }
        }

        /// <summary>
        /// 执行月结转
        /// </summary>
        public static string ExeCwJz(DateTime dCzsj)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                using (DSqlTrans daTrans = da.BeginTrans())
                {
                    dCzsj = new DateTime(dCzsj.Year, dCzsj.Month, 1).AddSeconds(-1); // 转为上月末
                    string sJzSj = string.Format("JZ{0}.{1}", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    string sRcBh = string.Format("RC{0}.{1}-", dCzsj.Year, dCzsj.Month.ToString().PadLeft(2, '0'));
                    // 写入历史
                    if (daTrans.Execute("INSERT INTO 物资历史(结转时间,物资ID,物资编号,期初数量,期初金额,入库数量,入库金额,出库数量,出库金额,库存数量,库存金额) SELECT @P0,物资ID,物资编号,期初数量,期初金额,入库数量,入库金额,出库数量,出库金额,库存数量,库存金额 FROM 物资库存 WHERE (SELECT ISNULL(MAX(结转时间),'') FROM 物资历史)<@P0", sJzSj) <= 0)
                    {
                        daTrans.Rollback();
                        return "月结转失败，可能已结转！";
                    }
                    // 更新库存
                    if (daTrans.Execute("UPDATE 物资库存 SET 期初数量=库存数量,期初金额=库存金额,入库数量=0,入库金额=0,出库数量=0,出库金额=0") <= 0)
                    {
                        daTrans.Rollback();
                        return "月结转失败！";
                    }
                    // 更新入库
                    if (daTrans.Execute("UPDATE 物资入库 SET 结转时间=@P0 WHERE 状态=9 AND 结转时间 IS NULL", sJzSj) < 0)
                    {
                        daTrans.Rollback();
                        return "月结转失败！";
                    }
                    // 更新出库
                    if (daTrans.Execute("UPDATE 物资出库 SET 结转时间=@P0 WHERE 状态=9 AND 结转时间 IS NULL", sJzSj) < 0)
                    {
                        daTrans.Rollback();
                        return "月结转失败！";
                    }
                    // 写入台账
                    if (daTrans.Execute("INSERT INTO 物资台账(物资ID,物资编号,入出编号,摘要说明,结存数量,结存金额,入出时间) SELECT 物资ID,物资编号,@P0+(SELECT REPLACE(STR(ISNULL(MAX(RIGHT(入出编号,4)),0)+1,4),' ','0') FROM 物资台账 WHERE 入出编号 LIKE @P0+'%'),'月末结转',库存数量,库存金额,@P1 FROM 物资库存", sRcBh, dCzsj) <= 0)
                    {
                        daTrans.Rollback();
                        return "月结转失败！";
                    }
                    if (!daTrans.Commit())
                    {
                        return "财务结转失败！";
                    }
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资出库查询总记录数
        /// </summary>
        public static DataSet GetWzCkCxByCxCount(DateTime dKsrq, DateTime dJsrq, string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资出库V A WHERE A.状态=9 AND A.审核时间 BETWEEN @P0 AND @P1 AND {0}", BGlobal.BuildLike(sWhere, "A.出库编号", "A.经领人", "A.物资编号", "A.物资名称", "A.规格型号", "A.用途")), dKsrq, dJsrq);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资出库查询
        /// </summary>
        public static DataSet GetWzCkCxByCx(DateTime dKsrq, DateTime dJsrq, string sWhere, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.出库编号, A.审核时间,A.经领人,A.物资编号,A.物资名称,A.规格型号,A.单位,A.原始数量,A.数量,A.用途,A.备注 FROM 物资出库V A WHERE A.状态=9 AND A.审核时间 BETWEEN @P0 AND @P1 AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资出库V B WHERE B.状态=9 AND B.审核时间 BETWEEN @P0 AND @P1 AND {3} ORDER BY B.出库编号,B.物资编号) ORDER BY A.出库编号,A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.出库编号", "A.经领人", "A.物资编号", "A.物资名称", "A.规格型号", "A.用途"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.出库编号", "B.经领人", "B.物资编号", "B.物资名称", "B.规格型号", "B.用途")), dKsrq, dJsrq);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资库存总记录数
        /// </summary>
        public static DataSet GetWzKcByCxCount(string sWhere, string sBh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资库存V A WHERE {0} AND A.物资编号 LIKE @P0+'%'", BGlobal.BuildLike(sWhere, "A.物资编号", "A.名称", "A.规格型号")), sBh);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资库存
        /// </summary>
        public static DataSet GetWzKcByCx(string sWhere, string sBh, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.物资ID,A.物资编号,A.名称,A.规格型号,A.单位,A.品牌厂家,A.库存数量,A.存放区域 FROM 物资库存V A WHERE {1} AND A.物资编号 LIKE @P0+'%' AND A.物资编号 NOT IN(SELECT TOP {2} B.物资编号 FROM 物资库存V B WHERE {3} AND B.物资编号 LIKE @P0+'%' ORDER BY B.物资编号) ORDER BY A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.物资编号", "A.名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.物资编号", "B.名称", "B.规格型号")), sBh);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资库存台账
        /// </summary>
        public static DataSet GetWzKcTzByCx(string sWzBh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT 入出时间,摘要说明,ISNULL(供货单位,经领人) 供货单位经领人,入库编号,入库数量,出库编号,出库数量,结存数量,用途,备注 FROM 物资台账V WHERE 物资编号=@P0 ORDER BY 入出编号", sWzBh);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库查询总记录数
        /// </summary>
        public static DataSet GetWzSfcCxByCxCount(string sJzYf, string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资历史V A WHERE A.结转时间=@P0 AND {0}", BGlobal.BuildLike(sWhere, "A.物资编号", "A.名称", "A.规格型号")), sJzYf);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资入库查询
        /// </summary>
        public static DataSet GetWzSfcCxByCx(string sJzYf, string sWhere, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.物资编号,A.名称,A.规格型号,A.单位,A.期初数量,A.期初金额,A.入库数量,A.入库金额,A.出库数量,A.出库金额,A.库存数量,A.库存金额 FROM 物资历史V A WHERE A.结转时间=@P0 AND {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资历史V B WHERE B.结转时间=@P0 AND {3} ORDER BY B.物资编号) ORDER BY A.物资编号", iPageSize, BGlobal.BuildLike(sWhere, "A.物资编号", "A.名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.物资编号", "B.名称", "B.规格型号")), sJzYf);
            }
        }
    }
}
